package work;

import com.project.bean.StudentBean;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class UserDaolmpl implements IUserDao {


    @Override
    public void add(User user) {
        Connection con=null;
        PreparedStatement ps =null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //建立连接
            //第一个参数为URL，localhost:12345 连接数据库主机的IP地址和端口
            //mydb为连接mysql数据库的名称，characterEncoding表示设置编码集
            //第二个参数为登录mysql的用户名，第三个参数为登录mysql的密码
            con= DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8", "root"
                    ,"lovo");

            //执行sql语句,问号表示占位符
            ps=con.prepareStatement(
                    "insert into t_user(u_name,u_pass,u_birthday,u_grade)values(?,?,?,?) ");
            //填充占位符
            ps.setObject(1,user.getName());
            ps.setObject(2, user.getPass());
            ps.setObject(3, user.getBirthday().toString());
            ps.setObject(4, user.getGrade());

            //更新数据库
            ps.executeUpdate();


            System.out.println(con);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                //关闭连接
                ps.close();
                con.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }



    @Override
    public void del(int id) {
        Connection con=null;
        PreparedStatement ps =null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //建立连接
            //第一个参数为URL，localhost:12345 连接数据库主机的IP地址和端口
            //mydb为连接mysql数据库的名称，characterEncoding表示设置编码集
            //第二个参数为登录mysql的用户名，第三个参数为登录mysql的密码
            con= DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8", "root"
                    ,"lovo");

            //执行sql语句,问号表示占位符
            ps=con.prepareStatement(
                    "delete from t_user where pk_user=?");
            //填充占位符
            ps.setObject(1,id);

            //更新数据库
            ps.executeUpdate();


            System.out.println(con);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                //关闭连接
                ps.close();
                con.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }

    }

    @Override
    public void update(int id, String pass, String grade) {
        Connection con=null;
        PreparedStatement ps =null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8",
                    "root","lovo");
            ps=con.prepareStatement("update t_user set u_pass=?,u_grade=?where pk_user=?");
            ps.setObject(1,pass);
            ps.setObject(2,grade);
            ps.setObject(3,id);


            ps.executeUpdate();


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    @Override
    public List<User> findAll() {
        List<User> list =new ArrayList<>();
        Connection con=null;//连接对象
        PreparedStatement ps =null;//sql语句执行对象
        ResultSet rs=null;//结果集对象
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8",
                    "root","lovo");
            ps=con.prepareStatement("select *from t_user");//执行sql语句
            rs=ps.executeQuery();//执行查询操作 得到结果集
            //遍历结果集,讲结果集指针指向下一条记录 ，如果没有下一条记录就返回false
            while (rs.next()){
                //创建学生对象，封装结果集中查询的类容
                User s=new User();
                //将pk_student列的值取出 封装为学生对象的属性
                s.setId(rs.getInt("pk_user"));
                s.setName(rs.getString("u_name"));
                s.setPass(rs.getString("u_pass"));
                s.setBirthday(LocalDate.parse(rs.getString("u_birthday")));
                s.setGrade(rs.getString("u_grade"));
                //将对象加入s集合
                list.add(s);
            }




        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        return  list;
    }

    @Override
    public User findByid(int id) {
        User b=null;
        Connection con=null;//连接对象
        PreparedStatement ps =null;//sql语句执行对象
        ResultSet rs=null;//结果集对象

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8",
                    "root","lovo");
            ps=con.prepareStatement("select *from t_user where pk_user=?");
            ps.setObject(1,id);

            rs=ps.executeQuery();

            if (rs.next()){
                b=new User();
                b.setId(rs.getInt("pk_user"));
                b.setName(rs.getString("u_name"));
                b.setPass(rs.getString("u_pass"));
                b.setBirthday(LocalDate.parse(rs.getString("u_birthday")));
                b.setGrade(rs.getString("u_grade"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return b;
    }

    @Override
    public List<User> findByName(String name) {
        List<User> list =new ArrayList<>();
        Connection con=null;//连接对象
        PreparedStatement ps =null;//sql语句执行对象
        ResultSet rs=null;//结果集对象

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8",
                    "root","lovo");
            ps=con.prepareStatement("select *from t_user where u_name like ? ");
            ps.setObject(1,"%"+name+"%");
            rs=ps.executeQuery();

            while (rs.next()){
                //创建学生对象，封装结果集中查询的类容
                User s=new User();
                //将pk_student列的值取出 封装为学生对象的属性
                s.setId(rs.getInt("pk_user"));
                s.setName(rs.getString("u_name"));
                s.setPass(rs.getString("u_pass"));
                s.setBirthday(LocalDate.parse(rs.getString("u_birthday")));
                s.setGrade(rs.getString("u_grade"));
                //将对象加入s集合
                list.add(s);
            }





        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        return list;
    }

    @Override
    public List<User> findByBirthday(LocalDate startDate, LocalDate endDate) {
        List<User> list =new ArrayList<>();
        Connection con=null;//连接对象
        PreparedStatement ps =null;//sql语句执行对象
        ResultSet rs=null;//结果集对象

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8",
                    "root","lovo");
            ps=con.prepareStatement("select *from t_user where  u_birthday>=?and  u_birthday<?");
            ps.setObject(1,startDate.toString());
            ps.setObject(2,endDate.toString());

            rs=ps.executeQuery();

            while (rs.next()){
                //创建学生对象，封装结果集中查询的类容
                User s=new User();
                //将pk_student列的值取出 封装为学生对象的属性
                s.setId(rs.getInt("pk_user"));
                s.setName(rs.getString("u_name"));
                s.setPass(rs.getString("u_pass"));
                s.setBirthday(LocalDate.parse(rs.getString("u_birthday")));
                s.setGrade(rs.getString("u_grade"));
                //将对象加入s集合
                list.add(s);
            }


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }





        return list;
    }

    @Override
    public List<User> findByGrade(String grade) {
        List<User> list =new ArrayList<>();
        Connection con=null;//连接对象
        PreparedStatement ps =null;//sql语句执行对象
        ResultSet rs=null;//结果集对象

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8",
                    "root","lovo");
            ps=con.prepareStatement("select *from t_user where u_grade=?");
            ps.setObject(1,grade);


            rs=ps.executeQuery();

            while (rs.next()){
                //创建学生对象，封装结果集中查询的类容
                User s=new User();
                //将pk_student列的值取出 封装为学生对象的属性
                s.setId(rs.getInt("pk_user"));
                s.setName(rs.getString("u_name"));
                s.setPass(rs.getString("u_pass"));
                s.setBirthday(LocalDate.parse(rs.getString("u_birthday")));
                s.setGrade(rs.getString("u_grade"));
                //将对象加入s集合
                list.add(s);
            }


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }





        return list;
    }

    @Override
    public List<User> login(String name, String pass) {
        List<User> list =new ArrayList<>();
        Connection con=null;//连接对象
        PreparedStatement ps =null;//sql语句执行对象
        ResultSet rs=null;//结果集对象

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8",
                    "root","lovo");
            ps=con.prepareStatement("select *from t_user where u_name=? and  u_pass=?");
            ps.setObject(1,name);
            ps.setObject(2,pass);


            rs=ps.executeQuery();

            if (rs.next()){
                System.out.println("登录成功");

            }else{
                System.out.println("登录失败");
            }


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }





        return list;
    }

    public static void main(String[] args) {
        IUserDao dao=new UserDaolmpl();
//        dao.add(new User("米哥","123",LocalDate.parse("2000-11-05"),"超级用户"));
//        dao.del(2);
//        dao.update(1,"1234","普通用户");
//       System.out.println(dao.findAll());
//        System.out.println(dao.findByName("赵"));
//        System.out.println(dao.findByBirthday(LocalDate.parse("1990-01-01"),LocalDate.parse("2001-01-01")));
//        System.out.println(dao.findByid(3));
//        System.out.println(dao.findByGrade("普通用户"));
        dao.login("米哥","123");
    }
}
